
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE   TRIGGER [dbo].[tb_posscorecashback_au] ON [dbo].[tb_posscorecashback] 
FOR UPDATE
AS

begin
  set nocount on
  if update(bstate)
  begin
	  if exists(select 1 from td_posmember v where v.id in (select d.memberid from inserted m
                                                      left join tb_posscorecashbackdtl d on d.bid=m.bid))
	  begin
	   update td_posmember
	     set td_posmember.score=isnull(td_posmember.score,0)-isnull(d.delscore,0),td_posmember.backamt=isnull(td_posmember.backamt,0)+isnull(d.backamt,0)
	   from td_posmember
	     inner join (
	                 select max(d.memberid) as memberid,sum(d.delscore) as delscore,sum(d.backamt) as backamt 
	                 from inserted m 
                     left join tb_posscorecashbackdtl d on d.bid=m.bid
                   where m.bstate=1
                   group by d.memberid
	                ) d on d.memberid=td_posmember.id 
    end
	  if exists(select 1 from td_posmember v where v.id in (select d.memberid from deleted m
                                                      left join tb_posscorecashbackdtl d on d.bid=m.bid))
    begin
	   update td_posmember
	     set td_posmember.score=isnull(td_posmember.score,0)+isnull(d.delscore,0),td_posmember.backamt=isnull(td_posmember.backamt,0)-isnull(d.backamt,0)
	   from td_posmember
	     inner join (
	                 select max(d.memberid) as memberid,sum(d.delscore) as delscore,sum(d.backamt) as backamt 
	                 from deleted m 
                     left join tb_posscorecashbackdtl d on d.bid=m.bid
                   where m.bstate=1
                   group by d.memberid
	                ) d on d.memberid=td_posmember.id 
	  end
  end
end





GO


